using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace axCommerce.Data
{
	[DataObject]
	[Serializable]
	public partial class Order
	{
		#region Constants

		private const string TableName = "[dbo].[Order]";

		#endregion

		#region Properties

		public int? OrderId { get; set; }
		public int? CustomerId { get; set; }
		public DateTime? CreatedDate { get; set; }
		public DateTime? LastModifiedDate { get; set; }
		public DateTime? CommitDate { get; set; }
		public DateTime? SendDate { get; set; }
		public bool? IsDeleted { get; set; }
		public bool? IsCommited { get; set; }

		#endregion

		#region Methods

		#region Mapping Methods

		protected void MapTo(DataSet ds)
		{
			if (ds == null)
				ds = new DataSet();

			if (ds.Tables[TableName] == null)
				ds.Tables.Add(TableName);

			ds.Tables[TableName].Columns.Add("OrderId", typeof (Int32));
			ds.Tables[TableName].Columns.Add("CustomerId", typeof (Int32));
			ds.Tables[TableName].Columns.Add("CreatedDate", typeof (DateTime));
			ds.Tables[TableName].Columns.Add("LastModifiedDate", typeof (DateTime));
			ds.Tables[TableName].Columns.Add("CommitDate", typeof (DateTime));
			ds.Tables[TableName].Columns.Add("SendDate", typeof (DateTime));
			ds.Tables[TableName].Columns.Add("IsDeleted", typeof (Boolean));
			ds.Tables[TableName].Columns.Add("IsCommited", typeof (Boolean));

			DataRow dr = ds.Tables[TableName].NewRow();

			dr["OrderId"] = OrderId ?? (object) DBNull.Value;
			dr["CustomerId"] = CustomerId ?? (object) DBNull.Value;
			dr["CreatedDate"] = CreatedDate ?? (object) DBNull.Value;
			dr["LastModifiedDate"] = LastModifiedDate ?? (object) DBNull.Value;
			dr["CommitDate"] = CommitDate ?? (object) DBNull.Value;
			dr["SendDate"] = SendDate ?? (object) DBNull.Value;
			dr["IsDeleted"] = IsDeleted ?? (object) DBNull.Value;
			dr["IsCommited"] = IsCommited ?? (object) DBNull.Value;
			ds.Tables[TableName].Rows.Add(dr);
		}

		protected void MapFrom(DataRow dr)
		{
			OrderId = dr["OrderId"] != DBNull.Value ? Convert.ToInt32(dr["OrderId"]) : OrderId = null;
			CustomerId = dr["CustomerId"] != DBNull.Value ? Convert.ToInt32(dr["CustomerId"]) : CustomerId = null;
			CreatedDate = dr["CreatedDate"] != DBNull.Value ? Convert.ToDateTime(dr["CreatedDate"]) : CreatedDate = null;
			LastModifiedDate = dr["LastModifiedDate"] != DBNull.Value
			                   	? Convert.ToDateTime(dr["LastModifiedDate"])
			                   	: LastModifiedDate = null;
			CommitDate = dr["CommitDate"] != DBNull.Value ? Convert.ToDateTime(dr["CommitDate"]) : CommitDate = null;
			SendDate = dr["SendDate"] != DBNull.Value ? Convert.ToDateTime(dr["SendDate"]) : SendDate = null;
			IsDeleted = dr["IsDeleted"] != DBNull.Value ? Convert.ToBoolean(dr["IsDeleted"]) : IsDeleted = null;
			IsCommited = dr["IsCommited"] != DBNull.Value ? Convert.ToBoolean(dr["IsCommited"]) : IsCommited = null;
		}

		private static List<Order> MapFrom(DataSet ds)
		{
			// Initialise Collection.
			List<Order> objects = new List<Order>();

			// Validation.
			if (ds == null)
				throw new ApplicationException("Cannot map to dataset null.");
			
			if (ds.Tables[TableName].Rows.Count == 0)
				return objects;

			if (ds.Tables[TableName] == null)
				throw new ApplicationException("Cannot find table [dbo].[Order] in DataSet.");

			if (ds.Tables[TableName].Rows.Count < 1)
				throw new ApplicationException("Table [dbo].[Order] is empty.");

			// Map DataSet to Instance.
			foreach (DataRow dr in ds.Tables[TableName].Rows)
			{
				var instance = new Order();
				instance.MapFrom(dr);
				objects.Add(instance);
			}

			// Return collection.
			return objects;
		}

		#endregion

		#region CRUD Methods

		private static Order Get(Int32 orderId)
		{
			Order instance = new Order();

			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspOrder_SELECT";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, orderId))
			{
				using (DataSet ds = db.ExecuteDataSet(dbCommand))
				{
					if (ds == null || ds.Tables[0].Rows.Count == 0)
						throw new ApplicationException(String.Format("Could not get Order ID:{0} from Database.", orderId));
					// Return results.
					ds.Tables[0].TableName = TableName;

					instance.MapFrom(ds.Tables[0].Rows[0]);
				}
			}
			return instance;
		}

		#region INSERT

		private void Insert(Int32? customerId, DateTime? createdDate, DateTime? lastModifiedDate,
		                    DateTime? commitDate, DateTime? sendDate, Boolean? isDeleted, Boolean? isCommited,
		                    DbTransaction transaction = null)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspOrder_INSERT";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, customerId, createdDate, lastModifiedDate, commitDate, sendDate, isDeleted, isCommited))
			{
				OrderId = Convert.ToInt32(transaction == null ? db.ExecuteScalar(dbCommand) : db.ExecuteScalar(dbCommand, transaction));
			}
		}

		/// <summary>
		/// Insert current Order to database.
		/// </summary>
		/// <param name="transaction">optional SQL Transaction</param>
		private void Insert(DbTransaction transaction = null)
		{
			Insert(CustomerId, CreatedDate, LastModifiedDate, CommitDate, SendDate, IsDeleted, IsCommited, transaction);
		}

		#endregion

		#region UPDATE

		private static void Update(Int32? orderId, Int32? customerId, DateTime? createdDate,
		                           DateTime? lastModifiedDate, DateTime? commitDate, DateTime? sendDate, Boolean? isDeleted,
		                           Boolean? isCommited, DbTransaction transaction = null)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspOrder_UPDATE";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand))
			{
				db.DiscoverParameters(dbCommand);
				dbCommand.Parameters["@orderId"].Value = orderId;
				dbCommand.Parameters["@customerId"].Value = customerId;
				dbCommand.Parameters["@createdDate"].Value = createdDate;
				dbCommand.Parameters["@lastModifiedDate"].Value = lastModifiedDate;
				dbCommand.Parameters["@commitDate"].Value = commitDate;
				dbCommand.Parameters["@sendDate"].Value = sendDate;
				dbCommand.Parameters["@isDeleted"].Value = isDeleted;
				dbCommand.Parameters["@isCommited"].Value = isCommited;

				if (transaction == null)
					db.ExecuteNonQuery(dbCommand);
				else
					db.ExecuteNonQuery(dbCommand, transaction);
			}
		}

		private static void Update(Order order)
		{
			order.Update();
		}

		private static void Update(Order order, DbTransaction transaction)
		{
			order.Update(transaction);
		}

		/// <summary>
		/// Updates changes to the database.
		/// </summary>
		/// <param name="transaction">optional SQL Transaction</param>
		private void Update(DbTransaction transaction = null)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspOrder_UPDATE";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand))
			{
				db.DiscoverParameters(dbCommand);
				dbCommand.Parameters["@orderId"].SourceColumn = "OrderId";
				dbCommand.Parameters["@customerId"].SourceColumn = "CustomerId";
				dbCommand.Parameters["@createdDate"].SourceColumn = "CreatedDate";
				dbCommand.Parameters["@lastModifiedDate"].SourceColumn = "LastModifiedDate";
				dbCommand.Parameters["@commitDate"].SourceColumn = "CommitDate";
				dbCommand.Parameters["@sendDate"].SourceColumn = "SendDate";
				dbCommand.Parameters["@isDeleted"].SourceColumn = "IsDeleted";
				dbCommand.Parameters["@isCommited"].SourceColumn = "IsCommited";

				using (DataSet ds = new DataSet())
				{
					MapTo(ds);
					ds.AcceptChanges();
					ds.Tables[0].Rows[0].SetModified();
					if (transaction == null)
						db.UpdateDataSet(ds, TableName, null, dbCommand, null, UpdateBehavior.Standard);
					else
						db.UpdateDataSet(ds, TableName, null, dbCommand, null, transaction);
				}
			}
		}

		#endregion

		#region DELETE

		private static void Delete(Int32? orderId, DbTransaction transaction)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspOrder_DELETE";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, orderId))
			{
				if (transaction != null)
				{
					db.ExecuteNonQuery(dbCommand, transaction);
				}
				else
				{
					db.ExecuteNonQuery(dbCommand);
				}
			}
		}

		/// <summary>
		/// Delete current Order from database.
		/// </summary>
		/// <param name="transaction">optional SQL Transaction</param>
		private void Delete(DbTransaction transaction = null)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspOrder_DELETE";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, OrderId))
			{
				if (transaction != null)
				{
					db.ExecuteNonQuery(dbCommand, transaction);
				}
				else
				{
					db.ExecuteNonQuery(dbCommand);
				}
			}
			OrderId = null;
		}

		#endregion

		#region SEARCH

		private static List<Order> Search(Int32? orderId = null, Int32? customerId = null, 
		                                  DateTime? createdDate = null, DateTime? lastModifiedDate = null,
		                                  DateTime? commitDate = null, DateTime? sendDate = null, Boolean? isDeleted = null,
		                                  Boolean? isCommited = null)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspOrder_SEARCH";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, orderId, customerId, createdDate, lastModifiedDate, commitDate, sendDate, isDeleted, isCommited))
			{
				using (DataSet ds = db.ExecuteDataSet(dbCommand))
				{
					ds.Tables[0].TableName = TableName;
					return MapFrom(ds);
				}
			}
		}


		private static List<Order> Search(Order searchObject)
		{
			return Search(searchObject.OrderId, searchObject.CustomerId, searchObject.CreatedDate,
			              searchObject.LastModifiedDate, searchObject.CommitDate, searchObject.SendDate, searchObject.IsDeleted,
			              searchObject.IsCommited);
		}

		#endregion

		#endregion

		#endregion
	}
}